/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package workforce.limousine.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import limousine.vo.CustomerInfo;

/**
 *
 * @author krisada
 */
public class CustomerSearchImpl implements CustomerSearcher {
    
    private DataSource dataSource = null;
    private int limit = 20;
    
    public CustomerSearchImpl() {
    }

    public List<CustomerInfo> search(int offset, String name) {
        List<CustomerInfo> list = new ArrayList<CustomerInfo>();
        String sname = "";
        if( name != null ) {
            if( name.trim().length() > 0 ) {
                sname = name;
            }
        }
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ");
        sql.append("IFNULL(o.CUSTOMER_ID, '-') AS CUSTOMER_ID, ");
        sql.append("IFNULL(o.CUSTOMER_NAME, '-') AS CUSTOMER_NAME,");
        sql.append("IFNULL(TRIM(o.BILL_NAME), '-') AS BILL_NAME,");
        sql.append("IFNULL(ok.LOOKUP_NAME, '-') AS LOOKUP_NAME ");
        sql.append("FROM customer o, lookup ok "); 
        sql.append("WHERE o.CUSTOMER_TYPE = ok.LOOKUP_VALUE");
        sql.append(" AND ok.LOOKUP_GROUP_ID = 'CUSTOMER_TYPE'");
        if( sname.trim().length() > 0 ) {
            sql.append(" AND o.CUSTOMER_NAME like '%");
            sql.append(sname);
            sql.append("%' ORDER BY o.CUSTOMER_NAME");
        }
        sql.append(" LIMIT ").append(limit);
        sql.append(" OFFSET ").append(offset);
        System.out.println("SQL=["+ sql.toString()+"]");
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            while (rs.next()) {
                CustomerInfo c = new CustomerInfo();
                c.setCustomerId(rs.getString(1));
                c.setCustomerName(rs.getString(2));
                c.setBillName(rs.getString(3));
                c.setCustomerTypeName(rs.getString(4));
                list.add(c);
            }
            rs.close();
            stmt.close();
            rs = null;
            stmt = null;
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return list;
    }
    
    public CustomerInfo findById(String id) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ");
        sql.append("IFNULL(o.CUSTOMER_ID, '-') AS CUSTOMER_ID, ");
        sql.append("IFNULL(o.CUSTOMER_NAME, '-') AS CUSTOMER_NAME,");
        sql.append("IFNULL(TRIM(o.BILL_NAME), '-') AS BILL_NAME,");
        sql.append("IFNULL(ok.LOOKUP_NAME, '-') AS LOOKUP_NAME ");
        sql.append("FROM customer o, lookup ok "); 
        sql.append("WHERE o.CUSTOMER_TYPE = ok.LOOKUP_VALUE");
        sql.append(" AND ok.LOOKUP_GROUP_ID = 'CUSTOMER_TYPE' AND o.CUSTOMER_ID = '").append(id).append("'");
        System.out.println("SQL=["+ sql.toString()+"]");
        Connection conn = null;
        CustomerInfo c = null;
        try {
            conn = dataSource.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            if (rs.next()) {
                c = new CustomerInfo();
                c.setCustomerId(rs.getString(1));
                c.setCustomerName(rs.getString(2));
                c.setBillName(rs.getString(3));
                c.setCustomerTypeName(rs.getString(4));
            }
            rs.close();
            stmt.close();
            rs = null;
            stmt = null;
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return c;
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public void setLimit(int limit) {
        this.limit = limit;
    }
    
    
}
